<?php

/*
 * Mysql 类
 * $DB_CONFIG 数据库配置变量名
 */

//$DB_CONFIG = array(
//    'charset' => 'UTF8',
//    'db' => 'dbname',
//    'prefix' => 'pre_',
//    'rw_separate' => false, //读写分离
//    'server' => array(
//        array('localhost', 3306, 'root', '', 0), //第一个为主服务器 负责写
//        array('localhost', 3306, 'root', '', 0),
//    )
//);

define('CLIENT_MULTI_RESULTS', 131072);

class Db {

    public $debug = true;
    // 数据库连接参数配置
    private $config = array();
    private $_prefix = '';
    // 数据库连接ID 支持多个连接
    private $linkID = array();
    // 当前连接ID
    private $_linkID = null;
    // 当前查询ID
    private $queryID = null;
    //查询语句参数
    private $options = array(
        'lock' => '',
        'field' => '*',
        'table' => '',
        'where' => '',
        'limit' => '',
        'join' => '',
        'order' => '',
        'group' => '',
        'having' => '',
        'distinct' => ''
    );
    // 当前SQL指令
    private $queryStr = '';
    // 最后插入ID
    private $lastInsID = null;
    // 返回或者影响记录数
    private $numRows = 0;
    // 错误信息
    private $error = '';
    // 数据库表达式
    private $comparison = array('eq' => '=', 'neq' => '!=', 'gt' => '>', 'egt' => '>=', 'lt' => '<', 'elt' => '<=', 'notlike' => 'NOT LIKE', 'like' => 'LIKE');
    //实例存储 getInstance用
    private static $_instance = array();

    private function __construct($config) {
        $this->config = $config;
        isset($this->config['charset']) || $this->config['charset'] = 'utf8';
        $this->_prefix = $this->config['prefix'] ? $this->config['prefix'] : '';
    }

    public static function I($config=array()) {
        if (empty($config)) {
            if (isset($GLOBALS['DB_CONFIG'])) {
                $config = $GLOBALS['DB_CONFIG'];
            } else {
                return false;
            }
        }
        $identify = md5(serialize($config));
        if (!isset(self::$_instance[$identify])) {
            self::$_instance[$identify] = new self($config);
        }
        return self::$_instance[$identify];
    }

    public function connect($linkNum=false) {
        if ($linkNum === false && !is_array($this->config['server'][0])) {
            $_config = $this->config['server'];
        } elseif (is_array($this->config['server'][$linkNum])) {
            $_config = $this->config['server'][$linkNum];
        } else {
            return false;
        }
        $linkKey = md5(serialize($_config));
        if (!isset($this->linkID[$linkKey])) {
            $host = $_config[0] . ($_config[1] ? ":{$_config[1]}" : '');
            if ($_config[4]) {
                $this->linkID[$linkKey] = mysql_pconnect($host, $_config[2], $_config[3], CLIENT_MULTI_RESULTS);
            } else {
                $this->linkID[$linkKey] = mysql_connect($host, $_config[2], $_config[3], true, CLIENT_MULTI_RESULTS);
            }
            if ($this->linkID[$linkKey]) {
                mysql_query("SET NAMES '" . $this->config['charset'] . "'", $this->linkID[$linkKey]);
                if (!empty($this->config['db']) && !mysql_select_db($this->config['db'], $this->linkID[$linkKey]))
                    return false;
            } else {
                return false;
            }
        }
        return $this->linkID[$linkKey];
    }

    protected function initConnect($linkNum=false) {
        if (is_array($this->config['server'][0])) { // 采用分布式数据库
            if (is_numeric($linkNum)) {
                $this->_linkID = $this->connect($linkNum);
            } elseif (!empty($this->config['server'])) {
                if ($this->config['rw_separate']) { // 默认主服务器是连接第一个数据库配置,读写分离，主服务器不承担读
                    $linkNum = floor(mt_rand(1, count($this->config['server']) - 1));   // 每次随机连接的数据库
                } else { // 主服务器也承担读
                    $linkNum = floor(mt_rand(0, count($this->config['server']) - 1));   // 每次随机连接的数据库
                }
                if (!$this->_linkID = $this->connect($linkNum)) {
                    unset($this->config['server'][$linkNum]);
                    $this->initConnect();
                }
            }
        } else { // 默认单数据库
            if (!$this->_linkID)
                $this->_linkID = $this->connect();
        }
    }

    public function switchConnect($linkNum) {
        if ($this->_linkID = $this->connect($linkNum)) {
            return true;
        } else {
            return false;
        }
    }

    public function free() {
        @mysql_free_result($this->queryID);
        $this->queryID = 0;
    }

    public function query($str) {
        $this->initConnect(false);
        if (!$this->_linkID)
            return false;
        $this->queryStr = $str;
        //释放前次的查询结果
        $this->queryID && $this->free();
        $this->queryID = mysql_query($str, $this->_linkID);
        if (false === $this->queryID) {
            $this->error();
            return false;
        } else {
            $this->numRows = mysql_num_rows($this->queryID);
            return $this->getAll();
        }
    }

    public function execute($str) {
        $this->initConnect(false);
        if (!$this->_linkID)
            return false;
        $this->queryStr = $str;
        //释放前次的查询结果
        $this->queryID && $this->free();
        $result = mysql_query($str, $this->_linkID);
        if (false === $result) {
            $this->error();
            return false;
        } else {
            $this->numRows = mysql_affected_rows($this->_linkID);
            $this->lastInsID = mysql_insert_id($this->_linkID);
            return $this->numRows;
        }
    }

    private function getAll() {
        //返回数据集
        $result = array();
        if ($this->numRows > 0) {
            while ($row = mysql_fetch_assoc($this->queryID)) {
                $result[] = $row;
            }
            mysql_data_seek($this->queryID, 0);
        }
        return $result;
    }

    public function close() {
        if (!empty($this->queryID))
            @mysql_free_result($this->queryID);
        if ($this->_linkID && !@mysql_close($this->_linkID)) {
            $this->error();
        }
        $this->_linkID = 0;
    }

    public function error($msg='') {
        $msg && $this->error .= "\n [ 自定义 ] : " . $msg;
        $this->error = mysql_error($this->_linkID);
        if ($this->debug && '' != $this->queryStr) {
            $this->error .= "\n [ SQL语句 ] : " . $this->queryStr;
        }
        return $this->error;
    }

    public function __destruct() {
        $this->close();
    }

    public function parseValue(&$value) {
        if (is_string($value)) {
            $value = '\'' . $this->escape_string($value) . '\'';
        } elseif (isset($value[0]) && is_string($value[0]) && strtolower($value[0]) == 'exp') {
            $value = $this->escape_string($value[1]);
        } elseif (is_null($value)) {
            $value = 'null';
        }
        return $value;
    }

    public function lock($lock=false) {
        if (!$lock)
            return;
        $this->options['lock'] = ' FOR UPDATE ';
        return $this;
    }

    public function field($fields) {
        if (is_array($fields)) {
            // 支持 'field1'=>'field2' 这样的字段别名定义
            $array = array();
            foreach ($fields as $key => $field) {
                if (!is_numeric($key))
                    $array[] = $this->addSpecialChar($key) . ' AS ' . $this->addSpecialChar($field);
                else
                    $array[] = $this->addSpecialChar($field);
            }
            $fieldsStr = implode(',', $array);
        }elseif (is_string($fields) && !empty($fields)) {
            $fieldsStr = $this->addSpecialChar($fields);
        } else {
            $fieldsStr = '*';
        }
        $this->options['field'] = $fieldsStr;
        return $this;
    }

    public function table($tables) {
        if (is_string($tables))
            $tables = explode(',', $tables);
        $array = array();
        foreach ($tables as $key => $table) {
            if (is_numeric($key)) {
                $table = strpos($table, $this->_prefix) === 1 ? $table : $this->_prefix . $table;
                $array[] = $this->addSpecialChar($table);
            } else {
                $key = strpos($key, $this->_prefix) === 1 ? $key : $this->_prefix . $key;
                $array[] = $this->addSpecialChar($key) . ' ' . $this->addSpecialChar($table);
            }
        }
        $this->options['table'] = implode(',', $array);
        return $this;
    }

    public function where($where) {
        $this->options['where'] = '';
        if (empty($where))
            return $this;
        if (is_string($where)) {
            // 直接使用字符串条件
            $this->options['where'] = $where;
        } elseif (is_array($where)) {
            $this->options['where'] = $this->parseWhereArray($where);
        } else {
            $this->error('WHERE_PARAMETER_ERROR' . ':' . $where);
        }
        $this->options['where'] && $this->options['where'] = ' WHERE ' . $this->options['where'];
        return $this;
    }

    public function parseWhereArray($array) {
        $whereStr = '';
        if (@$array['_logic']) {
            if (!in_array(strtoupper(trim($array['_logic'])), array('AND', 'OR', 'XOR')))
                die('SQL_LOGIC_ERROR' . ':' . $array['_logic']);
            $rule = strtoupper(trim($array['_logic']));
            $whereStrArray = array();
            unset($array['_logic']);
            foreach ($array as $val) {
                $whereStrArray[] = $this->parseWhereArray($val);
            }
            $whereStr = '(' . implode(' ' . $rule . ' ', $whereStrArray) . ')';
        } else {
            $whereStr = $this->parseWhereOperation($array);
        }
        return $whereStr;
    }

    private function parseWhereOperation($val) {
        if (!is_array($val))
            return $val;
        $key = $this->addSpecialChar($val[0]);
        if (is_string($val[1])) {
            if (preg_match('/^(EQ|NEQ|GT|EGT|LT|ELT|NOTLIKE|LIKE)$/i', $val[1])) { // 比较运算
                return $key . ' ' . $this->comparison[strtolower($val[1])] . ' ' . $this->parseValue($val[2]);
            } elseif ('exp' == strtolower($val[1])) { // 使用表达式
                return ' (' . $key . ' ' . $val[2] . ') ';
            } elseif (preg_match('/IN/i', $val[1])) { // IN 运算
                if (is_array($val[2])) {
                    array_walk($val[2], array($this, 'parseValue'));
                    $zone = implode(',', $val[2]);
                } else {
                    $zone = $val[2];
                }
                return $key . ' ' . strtoupper($val[1]) . ' (' . $zone . ')';
            } elseif (preg_match('/BETWEEN/i', $val[1])) { // BETWEEN运算
                $data = is_string($val[2]) ? explode(',', $val[2]) : $val[2];
                return ' (' . $key . ' ' . strtoupper($val[1]) . ' ' . $this->parseValue($data[0]) . ' AND ' . $this->parseValue($data[1]) . ' )';
            } else {
                die('_EXPRESS_ERROR_' . ':' . $val[1]);
            }
        } else {
            die('_EXPRESS_ERROR_' . ':' . $val[1]);
        }
    }

    public function limit($limit) {
        $this->options['limit'] = !empty($limit) ? ' LIMIT ' . $limit . ' ' : '';
        return $this;
    }

    public function join($join) {
        $joinStr = '';
        if (!empty($join)) {
            if (is_array($join)) {
                foreach ($join as $key => $_join) {
                    if (false !== stripos($_join, 'JOIN'))
                        $joinStr .= ' ' . $_join;
                    else
                        $joinStr .= ' LEFT JOIN ' . $_join;
                }
            }else {
                $joinStr .= ' LEFT JOIN ' . $join;
            }
        }
        $this->options['join'] = $joinStr;
        return $this;
    }

    public function order($order) {
        if (is_array($order)) {
            $array = array();
            foreach ($order as $key => $val) {
                if (is_numeric($key)) {
                    $array[] = $this->addSpecialChar($val);
                } else {
                    $array[] = $this->addSpecialChar($key) . ' ' . $val;
                }
            }
            $order = implode(',', $array);
        }
        $this->options['order'] = !empty($order) ? ' ORDER BY ' . $order : '';
        return $this;
    }

    public function group($group) {
        $this->options['group'] = !empty($group) ? ' GROUP BY ' . $group : '';
        return $this;
    }

    public function having($having) {
        $this->options['having'] = !empty($having) ? ' HAVING ' . $having : '';
        return $this;
    }

    public function distinct($distinct) {
        $this->options['distinct'] = !empty($distinct) ? ' DISTINCT ' : '';
        return $this;
    }

    public function parseOptions($options=array()) {
        if (!empty($options)) {
            foreach ($options as $key => $val) {
                $op_method = trim($key);
                if (method_exists($this, $op_method))
                    $this->$op_method($val);
            }
        }
    }

    public function resetOptions() {
        $this->options = array(
            'lock' => '',
            'field' => '*',
            'table' => '',
            'where' => '',
            'limit' => '',
            'join' => '',
            'order' => '',
            'group' => '',
            'having' => '',
            'distinct' => ''
        );
    }

    public function insert($data, $options=array()) {
        $this->parseOptions($options);
        foreach ($data as $key => $val) {
            $value = $this->parseValue($val);
            if (is_scalar($value)) { // 过滤非标量数据
                $values[] = $value;
                $fields[] = $this->addSpecialChar($key);
            }
        }
        $sql = 'INSERT INTO ' . $this->options['table'] . ' (' . implode(',', $fields) . ') VALUES (' . implode(',', $values) . ')' . $this->options['lock'];
        $this->resetOptions();
        return $this->execute($sql);
    }

    public function insertAll($datas, $options=array()) {
        if (!is_array($datas[0]))
            return false;
        $this->parseOptions($options);
        $fields = array_keys($datas[0]);
        array_walk($fields, array($this, 'addSpecialChar'));
        $values = array();
        foreach ($datas as $data) {
            $value = array();
            foreach ($data as $key => $val) {
                $val = $this->parseValue($val);
                if (is_scalar($val)) { // 过滤非标量数据
                    $value[] = $val;
                }
            }
            $values[] = '(' . implode(',', $value) . ')';
        }
        $sql = 'INSERT INTO ' . $this->options['table'] . ' (' . implode(',', $fields) . ') VALUES ' . implode(',', $values);
        $this->resetOptions();
        return $this->execute($sql);
    }

    public function replace($data, $options=array()) {
        $this->parseOptions($options);
        foreach ($data as $key => $val) {
            $value = $this->parseValue($val);
            if (is_scalar($value)) { // 过滤非标量数据
                $values[] = $value;
                $fields[] = $this->addSpecialChar($key);
            }
        }
        $sql = 'REPLACE INTO ' . $this->options['table'] . ' (' . implode(',', $fields) . ') VALUES (' . implode(',', $values) . ')';
        $this->resetOptions();
        return $this->execute($sql);
    }

    public function update($data, $options=array()) {
        $this->parseOptions($options);
        foreach ($data as $key => $val) {
            $value = $this->parseValue($val);
            if (is_scalar($value)) // 过滤非标量数据
                $set[] = $this->addSpecialChar($key) . '=' . $value;
        }
        $sql = 'UPDATE ' . $this->options['table'] . ' SET ' . implode(',', $set) . $this->options['where'] . $this->options['order'] . $this->options['limit'] . $this->options['lock'];
        $this->resetOptions();
        return $this->execute($sql);
    }

    public function delete($options=array()) {
        $this->parseOptions($options);
        $sql = 'DELETE FROM ' . $this->options['table'] . $this->options['where'] . $this->options['order'] . $this->options['limit'] . $this->options['lock'];
        $this->resetOptions();
        return $this->execute($sql);
    }

    public function select($options=array()) {
        if (isset($options['page'])) {
            // 根据页数计算limit
            list($page, $listRows) = explode(',', $options['page']);
            $listRows = $listRows ? $listRows : ((isset($options['limit']) && is_numeric($options['limit'])) ? $options['limit'] : 20);
            $offset = $listRows * ((int) $page - 1);
            $options['limit'] = $offset . ',' . $listRows;
        }
        $this->parseOptions($options);
        $sql = 'SELECT' . $this->options['distinct'] . ' ' . $this->options['field'] . ' FROM ' . $this->options['table'] . $this->options['join'] . $this->options['where'] . $this->options['group'] . $this->options['having'] . $this->options['order'] . $this->options['limit'] . $this->options['lock'];
        $this->resetOptions();
        return $this->query($sql);
    }

    public function find($options=array()) {
//        if (is_numeric($options) || is_string($options)) {
//            $where = $this->getPk() . '=\'' . $options . '\'';
//            $options = array();
//            $options['where'] = $where;
//        }
        // 总是查找一条记录
        $options = array_merge($options, array('limit' => 1));
        $resultSet = $this->select($options);
        if (empty($resultSet)) {// 查询结果为空
            return false;
        }
        return $resultSet[0];
    }

    public function count($options=array()) {
        $options = array_merge($options, array('field' => 'COUNT(*) as `_cnt`'));
        $resultSet = $this->find($options);
        return $resultSet ? $resultSet['_cnt'] : false;
    }

    public function addSpecialChar(&$value) {
        $value = trim($value);
        if (false !== strpos($value, ' ') || false !== strpos($value, ',') || false !== strpos($value, '*') || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`')) {
            //如果包含* 或者 使用了sql方法 则不作处理
        } else {
            $value = '`' . $value . '`';
        }
        return $value;
    }

    public function escape_string($str) {
        return mysql_escape_string($str);
    }

    public function getLastSql() {
        return $this->queryStr;
    }

    public function getError() {
        return $this->error;
    }

    public function getLastInsID() {
        return $this->lastInsID;
    }

}

?>